/**
 * @(#)QuerySqlParser.java 2010-4-1
 * 
 * Copyright (c) 2000-2010 by ChinanetCenter Corporation.
 *
 * All rights reserved.
 *
 * This software is the confidential and proprietary information of
 * ChinanetCenter Corporation ("Confidential Information").  You
 * shall not disclose such Confidential Information and shall use
 * it only in accordance with the terms of the license agreement
 * you entered into with ChinanetCenter.
 * 
 */

package com.szf.base.dao;

import java.util.ArrayList;
import java.util.List;
import java.util.StringTokenizer;

import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

@SuppressWarnings("unused")
public class QuerySqlParser {

	private static Logger logger = LoggerFactory.getLogger(QuerySqlParser.class);

	private static final byte BEFORE = 0;
	private static final byte AFTER = 1;

	private static final String FROM = " FROM ";
	private static final String WHERE = " WHERE ";
	private static final String ORDER = " ORDER ";
	private static final String GROUP = " GROUP ";
	private static final String HAVING = " HAVING ";
	private static final String BY = " BY ";
	private static final String ASC = " ASC ";
	private static final String DESC = " DESC ";

	/**
	 * 返回sqlStr 中的subid所在的位置(flag=0之前，flag=1之后)
	 * 
	 * @param sqlStr
	 *            输入的sql
	 * @param subid
	 *            关键字
	 * @param flag
	 *            _before = 0;after = 1;默认是after
	 * @return 返回 subid所在的before/after位置
	 */
	private static int getPos(String sqlStr, String subid, byte flag) {
		try {
			int sign = 0; // ()层数
			String tempStr;
			boolean isSingleQuotes = false; // 是否在''中
			int isValueStr = 0;
			for (int i = 0; i < sqlStr.length() - subid.length(); i++) {
				if (sqlStr.charAt(i) == '\'') {
					if (isSingleQuotes) {
						isSingleQuotes = false;
						isValueStr--;
						continue;
					}
					else {
						isSingleQuotes = true;
						isValueStr++;
						continue;
					}
				}
				if (sqlStr.charAt(i) == '(') {
					sign++;
				}
				if (sqlStr.charAt(i) == ')') {
					sign--;
				}
				if (isSingleQuotes || isValueStr != 0 || sign != 0) {
					continue;
				}

				tempStr = sqlStr.substring(i, i + subid.length());

				if (subid.equalsIgnoreCase(tempStr)) {
					if (flag == BEFORE) {
						return i;
					}
					else if (flag == AFTER) {
						return i + subid.length();
					}
					else {
						// after 默认是subid 之后的位置
						return i + subid.length();
					}
				}
			}
			return -1;
		}
		catch (Exception e) {
			logger.error("", e);
			return -1;
		}
	}

	/**
	 * 获取关键字 subid nextid 字段所在的位置
	 * 
	 * @param sqlStr
	 * @param subid
	 * @param nextid
	 * @return 获取关键字 subid nextid 字段所在的位置
	 */
	private static int getPos(String sqlStr, String subid, String nextid) {
		try {
			int sign = 0;
			String tempStr;
			boolean isSingleQuotes = false;
			int isValueStr = 0;
			for (int i = 0; i < sqlStr.length() - subid.length(); i++) {
				if (sqlStr.charAt(i) == '\'') {
					if (isSingleQuotes) {
						isSingleQuotes = false;
						isValueStr--;
						continue;
					}
					else {
						isSingleQuotes = true;
						isValueStr++;
						continue;
					}
				}
				if (sqlStr.charAt(i) == '(') {
					sign++;
				}
				if (sqlStr.charAt(i) == ')') {
					sign--;
				}
				if (isSingleQuotes || isValueStr != 0 || sign != 0) {
					continue;
				}
				tempStr = sqlStr.substring(i, i + subid.length());

				if (subid.equalsIgnoreCase(tempStr) || subid.equalsIgnoreCase(tempStr.trim())) {
					return sqlStr.toUpperCase().indexOf(nextid, i) + nextid.length();
				}
			}
			return -1;
		}
		catch (Exception e) {
			logger.error("", e);
			return -1;
		}
	}

	/**
	 * 取ASC/DESC之前的位置
	 * 
	 * @param sqlStr
	 *            输入的sql
	 * @return -1 异常不处理
	 */
	private static int getBeforeAscPosition(String sqlStr) {
		try {
			int sign = 0;
			String tempStr;
			boolean isSingleQuotes = false;
			int isValueStr = 0;
			int checkLength = sqlStr.length() - " ASC".length() + 1;
			for (int i = 0; i < checkLength; i++) {
				if (sqlStr.charAt(i) == '\'') {
					if (isSingleQuotes) {
						isSingleQuotes = false;
						isValueStr--;
						continue;
					}
					else {
						isSingleQuotes = true;
						isValueStr++;
						continue;
					}
				}
				if (isSingleQuotes || isValueStr != 0) {
					continue;
				}

				if (sqlStr.charAt(i) == '(') {
					sign++;
					continue;
				}
				if (sqlStr.charAt(i) == ')') {
					sign--;
					continue;
				}

				if (sign != 0) {
					continue;
				}

				tempStr = sqlStr.substring(i, i + " ASC".length());

				//if (" ASC".equalsIgnoreCase(tempStr)||
				//	"ASC".equalsIgnoreCase(tempStr.trim())) {
				if (" ASC".equalsIgnoreCase(tempStr)) {
					return i;
				}
			}

			sign = 0;
			isSingleQuotes = false;
			isValueStr = 0;
			checkLength = sqlStr.length() - " DESC".length() + 1;
			for (int i = 0; i < checkLength; i++) {
				if (sqlStr.charAt(i) == '\'') {
					if (isSingleQuotes) {
						isSingleQuotes = false;
						isValueStr--;
						continue;
					}
					else {
						isSingleQuotes = true;
						isValueStr++;
						continue;
					}
				}
				if (isSingleQuotes || isValueStr != 0) {
					continue;
				}

				if (sqlStr.charAt(i) == '(') {
					sign++;
					continue;
				}
				if (sqlStr.charAt(i) == ')') {
					sign--;
					continue;
				}

				if (sign != 0) {
					continue;
				}

				tempStr = sqlStr.substring(i, i + " DESC".length());

				//if (" DESC".equalsIgnoreCase(tempStr)||
				//	"DESC".equalsIgnoreCase(tempStr.trim())) {
				if (" DESC".equalsIgnoreCase(tempStr)) {
					return i;
				}
			}
			return -1;
		}
		catch (Exception ex) {
			logger.error("", ex);
			return -1;
		}
	}

	/**
	 * 去除结尾的分号";"
	 */
	private static String trimSemicolon(String sql) {
		if (StringUtils.isEmpty(sql)) {
			return "";
		}
		String sqlStr = sql.trim();
		if (sqlStr.endsWith(";")) {
			sqlStr = sqlStr.substring(0, sqlStr.length() - 1);
		}
		return sqlStr;
	}

	/**
	 * 取GROUP BY,ORDER BY 之前所在位置
	 * 
	 * @param sqlStr
	 * @return -1: 异常时返回-1
	 */
	private static int getBeforeGroupOrderPosition(String sqlStr) {
		try {
			int sign = 0;
			String tempStr;
			boolean isSingleQuotes = false;
			int isValueStr = 0;
			//" GROUP "和" ORDER "长度相等，都为7
			for (int i = 0; i < sqlStr.length() - 7; i++) {

				if (sqlStr.charAt(i) == '\'') {
					if (isSingleQuotes) {
						isSingleQuotes = false;
						isValueStr--;
						continue;
					}
					else {
						isSingleQuotes = true;
						isValueStr++;
						continue;
					}
				}
				if (isSingleQuotes || isValueStr != 0) {
					continue;
				}

				if (sqlStr.charAt(i) == '(') {
					sign++;
				}
				if (sqlStr.charAt(i) == ')') {
					sign--;
				}
				if (sign != 0) {
					continue;
				}
				//" GROUP "和" ORDER "长度相等，都为7
				tempStr = sqlStr.substring(i, i + 7);

				if (" ORDER ".equalsIgnoreCase(tempStr) || " GROUP ".equalsIgnoreCase(tempStr)
						|| "ORDER".equalsIgnoreCase(tempStr.trim())
						|| "GROUP".equalsIgnoreCase(tempStr.trim())) {
					return i;
					// return sqlStr.toUpperCase().indexOf(" BY ", i) + 4;
				}
			}
			return -1;
		}
		catch (Exception ex) {
			logger.error("", ex);
			return -1;
		}
	}

	/**
	 * 取 WHERE ORDER BY GROUP BY之前 的位置 WHERE , GROUP , ORDER
	 * 
	 * @param sqlStr
	 * @return
	 */
	private static int getBeforeWhereOrderGroupPosition(String sqlStr) {
		try {
			int sign = 0;
			String tempStr;
			boolean isSingleQuotes = false;
			int isValueStr = 0;
			//" WHERE "长度为7
			for (int i = 0; i < sqlStr.length() - 7; i++) {
				if (sqlStr.charAt(i) == '\'') {
					if (isSingleQuotes) {
						isSingleQuotes = false;
						isValueStr--;
						continue;
					}
					else {
						isSingleQuotes = true;
						isValueStr++;
						continue;
					}
				}
				if (isSingleQuotes || isValueStr != 0) {
					continue;
				}

				if (sqlStr.charAt(i) == '(') {
					sign++;
				}
				if (sqlStr.charAt(i) == ')') {
					sign--;
				}
				if (sign != 0) {
					continue;
				}
				//" WHERE "长度为7
				tempStr = sqlStr.substring(i, i + 7);

				if (" WHERE ".equalsIgnoreCase(tempStr) || " GROUP ".equalsIgnoreCase(tempStr)
						|| " ORDER ".equalsIgnoreCase(tempStr)
						|| "WHERE".equalsIgnoreCase(tempStr.trim())
						|| "GROUP".equalsIgnoreCase(tempStr.trim())
						|| "ORDER".equalsIgnoreCase(tempStr.trim())) {
					return i;
				}
			}
			return -1;
		}
		catch (Exception ex) {
			logger.error("", ex);
			return -1;
		}
	}

	/**
	 * 获取select 条件
	 * 
	 * @param whersql
	 *            String
	 * @return 异常时返回 "*"
	 */
	public static String getSelect(String sqlStr) {
		if (StringUtils.isEmpty(sqlStr)) {
			return "";
		}
		String tempStr = sqlStr.toUpperCase();
		try {
			// from所在的位置
			int fromPos = getPos(sqlStr, FROM, BEFORE);
			if (fromPos == -1) {
				logger.error("[getSelect].错误" + sqlStr + "], 没有from关键字!");
				return "";
			}
			else {
				return sqlStr.substring(tempStr.indexOf("SELECT ") + "SELECT ".length(), fromPos);
			}
		}
		catch (Exception ex) {
			logger.error("", ex);
			return "*";
		}
	}

	/**
	 * 获取from 字句
	 * 
	 * @param sqlStr
	 * @return
	 */
	public static String getFrom(String sql) {
		if (StringUtils.isEmpty(sql)) {
			return "";
		}
		try {
			String sqlStr = trimSemicolon(sql);
			String tempStr = sqlStr.toUpperCase();

			int fromPos = getPos(tempStr, FROM, AFTER);

			if (fromPos == -1) {
				logger.error("[getFrom].错误" + sqlStr + "], 没有from关键字!");
				return "";
			}
			else {
				int wogPos = getBeforeWhereOrderGroupPosition(tempStr);

				if (wogPos == -1) { // where , group , order , having
					return sqlStr.substring(fromPos - 1);
				}
				else {
					return sqlStr.substring(fromPos - 1, wogPos);
				}
			}
		}
		catch (Exception ex) {
			logger.error("", ex);
			return "";
		}
	}

	/**
	 * 获取 {WHERE} .. {group} {order} 之间的字符串
	 * 
	 * @param whersql
	 *            String
	 * @return String
	 */
	public static String getWhere(String sqlStr) {
		if (StringUtils.isEmpty(sqlStr)) {
			return "";
		}
		try {
			sqlStr = trimSemicolon(sqlStr);
			String tempStr = sqlStr.toUpperCase();
			// where 关键字之后的位置
			int wherePos = getPos(sqlStr, WHERE, AFTER);

			if (wherePos == -1) {
				logger.error("[getWhere]. " + sqlStr + "],没有where关键字!");
				return "";
			}
			else {
				// 是否有排序/分组的情况
				int goPos = getBeforeGroupOrderPosition(tempStr);

				if (goPos == -1) { // where , group , order ,没有考虑 having
					return sqlStr.substring(wherePos - 1);
				}
				else {
					return sqlStr.substring(wherePos - 1, goPos);
				}
			}
		}
		catch (Exception ex) {
			logger.error("", ex);
			return "*";
		}
	}

	/**
	 * @param sqlStr
	 * @return
	 */
	private static String getQueryWhere(String sqlStr) {
		if (StringUtils.isEmpty(sqlStr)) {
			return "";
		}
		try {
			sqlStr = trimSemicolon(sqlStr);
			String tempStr = sqlStr.toUpperCase();
			int goPos = getBeforeGroupOrderPosition(tempStr);

			if (goPos == -1) { // where , group , order , having
				return sqlStr;
			}
			else {
				return sqlStr.substring(0, goPos);
			}
		}
		catch (Exception ex) {
			logger.error("[getWhere] :" + ex.getMessage(), ex);
			return "*";
		}
	}

	/**
	 * 获取group by之后的分组条件
	 * 
	 * @param sqlStr
	 * @return 异常时返回""
	 */
	public static String getGroup(String sqlStr) {
		try {
			sqlStr = trimSemicolon(sqlStr);
			int groupPos = getPos(sqlStr, GROUP, BY);

			if (groupPos == -1) {
				return "";
			}
			else {
				int orderPos = getPos(sqlStr, ORDER, BEFORE);
				if (orderPos == -1) {
					return sqlStr.substring(groupPos - 1);
				}
				else {
					return sqlStr.substring(groupPos - 1, orderPos);
				}
			}
		}
		catch (Exception ex) {
			logger.error("", ex);
			return "";
		}
	}

	/**
	 * ORDER, ASC/DESC 获取排序条件
	 * 
	 * @param sqlstr
	 *            String
	 * @return String
	 */
	public static String getOrder(String sqlStr) {
		try {
			sqlStr = trimSemicolon(sqlStr);
			int orderPos = getPos(sqlStr, ORDER, BY);
			if (orderPos == -1) {
				return "";
			}
			else {
				int groupPos = getPos(sqlStr, GROUP, BY);
				if (groupPos == -1 || orderPos >= groupPos) {
					return sqlStr.substring(orderPos).trim();
				}
				else {
					return sqlStr.substring(orderPos, groupPos).trim();
				}
			}
		}
		catch (Exception ex) {
			logger.error("", ex);
			return "";
		}
	}

	/**
	 * select count(*) from ... where ... 组合求总记录的sql语句
	 * 
	 * @param whersql
	 *            String
	 * @return String
	 */
	public static String getCountSql(String sqlStr) {
		return getCountSql(sqlStr, "*");
	}

	/**
	 * select count() from ... where ... 组合求总记录的sql语句,自定义计算列
	 * 
	 */
	public static String getCountSql(String sqlStr, String countcolumn) {
		try {
			//			int fromPos = getPos(sqlStr, FROM, BEFORE);
			//
			//			int orderPos = getPos(sqlStr, ORDER, BEFORE);
			//
			//			if (fromPos == -1) {
			//				logger.error("[getCountSql]. 错误的sql " + sqlStr + "], 没有from关键字!");
			//				return sqlStr;
			//			}
			//			else {
			//				if (orderPos != -1) {
			//					return "select count(" + countcolumn + ") "
			//							+ sqlStr.substring(fromPos, orderPos);
			//				}
			//				else {
			//					return "select count(" + countcolumn + ") " + sqlStr.substring(fromPos);
			//				}
			//			}
			return "select count(" + countcolumn + ") from (" + sqlStr + ") temp";
		}
		catch (Exception ex) {
			logger.error("", ex);
			return "";
		}
	}

	/**
	 * 获取查询表名及其别名
	 */
	public static List<QueryTable> getFromTables(String sqlStr) {
		List<QueryTable> result = new ArrayList<QueryTable>(0);
		String sqlFrom = getFrom(sqlStr);
		if (StringUtils.isEmpty(sqlFrom)) {
			return result;
		}
		// 将所有" join "转化成","，便于查找
		sqlFrom = sqlFrom.replaceAll(" [Jj][Oo][Ii][Nn] ", ",");
		StringTokenizer st = new StringTokenizer(sqlFrom.trim(), ",");
		for (; st.hasMoreTokens();) {
			String table = st.nextToken().trim();
			// t1 as a inner|left|right,t1 a,t1 as a,t1 on
			StringTokenizer st2 = new StringTokenizer(table);
			// 第一个总是表名
			String tableName = st2.nextToken();
			String aliasName = tableName;
			// 有间隔，判断是否为关键字，可能有别名
			if (st2.hasMoreTokens()) {
				String next = st2.nextToken();
				if ("AS".equalsIgnoreCase(next)) {
					// 一定有别名
					aliasName = st2.nextToken();
				}
				else if ("LEFT".equalsIgnoreCase(next) || "RIGHT".equalsIgnoreCase(next)
						|| "INNER".equalsIgnoreCase(next) || "ON".equalsIgnoreCase(next)) {
					// 没有别名
				}
				else {
					// 别名紧跟表名
					aliasName = next;
				}
			}
			result.add(new QueryTable(tableName, aliasName));
		}
		return result;
	}

	/**
	 * 添加where条件
	 * 
	 * @param sqlStr
	 *            String
	 * @param appendWhere
	 *            String
	 * @return String
	 */
	public static String getQuerySql(String sqlStr, String appendWhere) {
		if (StringUtils.isEmpty(appendWhere)) {
			return sqlStr;
		}
		try {
			// 去掉前置的where
			if (appendWhere.trim().toLowerCase().startsWith("where ")) {
				appendWhere = appendWhere.substring(6);
			}

			// 去掉前置的and
			if (appendWhere.trim().toLowerCase().startsWith("and ")) {
				appendWhere = appendWhere.substring(4);
			}

			String appWhere = getQueryWhere(appendWhere);// 动态查询的where
			String appOrder = getOrder(appendWhere);

			String sqlselect = getSelect(sqlStr); // SELECT
			String sqlfrom = getFrom(sqlStr); // FROM
			String sqlwhere = getWhere(sqlStr); // 原有的WHERE 条件
			String sqlgroup = getGroup(sqlStr); // GROUP 分组条件
			String sqlorder = getOrder(sqlStr); // ORDER ASC/DESC

			StringBuilder opSql = new StringBuilder(sqlselect.length() * 2);
			opSql.append("SELECT ").append(sqlselect).append(" FROM ").append(sqlfrom);
			if (StringUtils.isEmpty(appWhere)) {
				if (StringUtils.isNotEmpty(sqlwhere)) {
					opSql.append(" WHERE ").append(sqlwhere);
				}
			}
			else {
				if (StringUtils.isNotEmpty(sqlwhere)) {
					opSql.append(" WHERE (").append(sqlwhere).append(") AND (").append(appWhere)
							.append(")");
				}
				else {
					opSql.append(" WHERE ").append(appWhere);
				}
			}

			if (StringUtils.isNotEmpty(sqlgroup)) {
				opSql.append(" GROUP BY ").append(sqlgroup);
			}
			if (StringUtils.isNotEmpty(sqlorder)) {
				if (StringUtils.isEmpty(appOrder)) {
					opSql.append(" ORDER BY ").append(sqlorder);
				}
				else {
					opSql.append(" ORDER BY ").append(sqlorder).append(", ").append(appOrder);
				}
			}
			else {
				if (StringUtils.isNotEmpty(appOrder)) {
					opSql.append(" ORDER BY ").append(appOrder);
				}
			}
			return opSql.toString();
		}
		catch (Exception ex) {
			logger.error("", ex);
			return sqlStr;
		}
	}

	public static boolean isFilterGroupBy(String sqlStr) {
		String groupby = " GROUP BY ";
		String tempStr;
		boolean isFilter = false;

		for (int i = 0, len = sqlStr.length() - groupby.length(); i < len; i++) {
			tempStr = sqlStr.substring(i, i + groupby.length());

			if (tempStr.equalsIgnoreCase(groupby)) {
				if (!isGroupByInline(i + groupby.length(), i, sqlStr)) {
					isFilter = true;
					break;
				}
			}
		}

		return isFilter;
	}

	private static boolean isGroupByInline(int pos1, int pos2, String sqlStr) {
		boolean isInline = false;
		boolean isRightExists = false;
		boolean isLeftExists = false;

		for (int i = pos1, len = sqlStr.length(), sign = 0; i < len; i++) {
			if (sqlStr.charAt(i) == '(') {
				sign++;
			}
			else if (sqlStr.charAt(i) == ')') {
				if (sign == 0) {
					isRightExists = true;
					break;
				}
				else if (sign > 0) {
					sign--;
				}
			}
		}

		if (isRightExists) {
			for (int i = pos2, sign = 0; i > 0; i--) {
				if (sqlStr.charAt(i) == ')') {
					sign++;
				}
				else if (sqlStr.charAt(i) == '(') {
					if (sign == 0) {
						isLeftExists = true;
						break;
					}
					else if (sign > 0) {
						sign--;
					}
				}
			}
		}

		if (isLeftExists && isRightExists) {
			isInline = true;
		}

		return isInline;
	}

	public static String replaceAlias(String sqlStr, String fromAlias, String toAlias) {
		return sqlStr.replaceAll("([ ,=])" + fromAlias + "([ \\(\\),\\.])", "$1" + toAlias + "$2");
	}

	public static void main(String[] args) {
		String sqlstr = " select a,da.b, c,"
				+ " (select x, y, decode(z,1,'A', 2, 'B', 'C'), name, password, to_char(birthdate, 'yyyy-mm-dd') as bbdate from m, n where m.x=n.y) "
				+ "from t1 da, t2 as db, t3 " + "where da.a=t2.a and t1.a=t3.a and t2.c=t3.c "
				+ "group by da.a, x, z " + "order by z asc, y desc, da.b asc";

		System.out.println("sql = \n" + sqlstr);
		System.out.println("\nSELECT  = \n" + getSelect(sqlstr));
		System.out.println("\nFROM = \n" + getFrom(sqlstr));
		System.out.println("\nWHERE   = \n" + getWhere(sqlstr));
		System.out.println("\nGROUP   = \n" + getGroup(sqlstr));
		System.out.println("\nORDER   = \n" + getOrder(sqlstr));

		System.out.println("\nCOUNT SQL GROUP = \n" + getCountSql(sqlstr));

		System.out.println("\nOPSQL   = \n"
				+ getQuerySql(sqlstr, "where x=1 and y=2 group by y order by x desc"));

		String str2 = "select * from t1 a inner join t2 b on a.a1=b.b2, t3 c,t4 as d where a.1=c.3 and c.1=d.4;";

		List<QueryTable> tables = getFromTables(str2);
		for (QueryTable table : tables) {
			System.out.println("FROM " + table.getTableName() + " AS " + table.getAliasName());
		}
		System.out.println(replaceAlias(sqlstr, "da", "dda"));

		System.out.println(trimSemicolon(""));
		System.out.println(trimSemicolon("abc"));
		System.out.println(trimSemicolon("abc; "));
		System.out.println(trimSemicolon("abc ; "));
	}

}
